SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE  PROCEDURE [dbo].[SP_TTSUBR_FINA_Q03]            
/*--------------------------------------------------------*/            
/*---Empresa               OFISIS S.A.                  --*/            
/*---Cliente               OFISIS S.A.                  --*/            
/*---Sistema               Tesoreria					--*/            
/*---M«dulo                Empresa						--*/            
/*---Programa              FLUJO DE CAJA MENSUAL		--*/            
/*---Script                SP_TTSUBR_FINA_Q03.sql		--*/            
/*---Nombre SP             SP_TTSUBR_FINA_Q03			--*/            
/*---Desarrollado por      Carlos Marquez				--*/            
/*---Fecha Creaci«n        01/08/2006					--*/            
/*---Base Datos            SQL SERVER					--*/            
/*---Versi«n               2005							--*/            
/*---Invoca a SP          								--*/            
/*--------------------------------------------------------*/            
/*--------------------------------------------------------*/            
/*---Modificado 1 por      Carlos Marquez Escobar		--*/            
/*---Fecha Modificaci«n    01/08/2006					--*/             
/*---Detalle Modificaci«n  Recalculo de los Stocks		--*/            
/*--------------------------------------------------------*/            
/*--------------------------------------------------------*/            
/*---Drop Proc SP_TTSUBR_FINA_Q03      --*/            
/*---Grant Execute on SP_TTSUBR_FINA_Q03 to public  --*/             
/*----- EXEC SP_TTSUBR_FINA_Q03 '01', 'DOL', '01/01/2006', 3.55, 1, 2006, 12, 2006, 13767.700000--*/            
/*--------------------------------------------------------*/            
/*            
 ST_REPO indica si Muestra Totales o No            
 ST_DETA indica si Muestra Detalles            
*/            
@ISCO_EMPR  TD_VC_002,            
@ISCO_MONE  TD_VC_003,            
@IDFE_ULTI_TICA TD_DT_001, -- Ultima Fecha del mes Anterior, con Tipo de Cambio            
@INFA_TIPO_CAMB TD_NU_015_006, -- Tipo de Cambio x Calculo de Saldos Iniciales            
@INNU_MESE_INIC TD_IN_001,            
@INNU_ANIO_INIC TD_IN_001,            
@INNU_MESE_FINA TD_IN_001,            
@INNU_ANIO_FINA TD_IN_001,  
@INIM_SALD_INIC TD_NU_016_004  
As            
            
--- Tabla - Carga Importes Mes x Mes            
CREATE TABLE #TWFLUJ_CAJA_Q03_0            
(             
CO_EMPR varchar(03) NULL,            
CO_ARUB varchar(03) NULL ,            
CO_RUBR varchar(03) NULL ,            
CO_SUBR varchar(03) NULL ,            
FE_OPER datetime NULL,            
FA_TIPO_CAMB numeric(9,4) DEFAULT 0,            
CO_MONE varchar(03) NULL ,            
IM_MOVI numeric(16,4) DEFAULT 0 )            
             
--- Tabla Detalle de Contiene Valores Mes por Mes            
CREATE TABLE #TWFLUJ_CAJA_Q03_1            
(             
CO_EMPR varchar(03) NULL,            
CO_ARUB varchar(03) NULL ,            
CO_RUBR varchar(03) NULL ,            
CO_SUBR varchar(03) NULL ,            
IM_ME01 numeric(16,4) DEFAULT 0,            
IM_ME02 numeric(16,4) DEFAULT 0,            
IM_ME03 numeric(16,4) DEFAULT 0,            
IM_ME04 numeric(16,4) DEFAULT 0,            
IM_ME05 numeric(16,4) DEFAULT 0,            
IM_ME06 numeric(16,4) DEFAULT 0,            
IM_ME07 numeric(16,4) DEFAULT 0,            
IM_ME08 numeric(16,4) DEFAULT 0,            
IM_ME09 numeric(16,4) DEFAULT 0,            
IM_ME10 numeric(16,4) DEFAULT 0,            
IM_ME11 numeric(16,4) DEFAULT 0,            
IM_ME12 numeric(16,4) DEFAULT 0,            
NU_MESE varchar(02)            
 )            
--- Tabla Trabajo a Mostrar--            
CREATE TABLE #TWFLUJ_CAJA_Q03_3            
(             
DE_ARUB varchar(100) NULL ,            
IM_ME01 numeric(16,4) DEFAULT 0,            
IM_ME02 numeric(16,4) DEFAULT 0,            
IM_ME03 numeric(16,4) DEFAULT 0,            
IM_ME04 numeric(16,4) DEFAULT 0,  
IM_ME05 numeric(16,4) DEFAULT 0,            
IM_ME06 numeric(16,4) DEFAULT 0,            
IM_ME07 numeric(16,4) DEFAULT 0,            
IM_ME08 numeric(16,4) DEFAULT 0,            
IM_ME09 numeric(16,4) DEFAULT 0,            
IM_ME10 numeric(16,4) DEFAULT 0,            
IM_ME11 numeric(16,4) DEFAULT 0,            
IM_ME12 numeric(16,4) DEFAULT 0,            
NU_ORDE Int null,            
ST_FLAG varchar(01) null,          
TI_DETA varchar(02) null     
 )            
DECLARE            
            
@VNNU_MESE  TD_IN_001,            
@VNNU_ANIO  TD_IN_001,            
@VSNU_MESE  TD_VC_002,            
@VSNU_ANIO  VARCHAR(4),            
@VNNU_CONT_MESE TD_IN_001,            
@VSDE_SELE VARCHAR(6000),            
@VSCO_EMPR VARCHAR (50),            
@VSDE_CAMP VARCHAR(10),            
@VSDE_CAMP_ANTE VARCHAR(10),  
  
            
@VSNU_MESE_INIC TD_VC_002,            
@VSNU_ANIO_INIC TD_VC_004,            
            
/* Agrupaciones */            
@CSCO_ARUB  TD_VC_003,            
@CSDE_ARUB  TD_VC_100,            
@CSST_REPO_ARUB TD_VC_001,            
@CNNU_ORDE_ARUB TD_IN_001,            
@CSST_FLUJ_ECON TD_VC_001,            
@CSST_FLUJ_FINA TD_VC_001,            
@CSST_SALD_INIC TD_VC_001,            
@CSST_DETA_ARUB TD_VC_001,            
/* Rubros */            
@CSCO_RUBR  TD_VC_003,            
@CSDE_RUBR  TD_VC_100,            
@CSST_REPO_RUBR TD_VC_001,            
@CNNU_ORDE_RUBR TD_IN_001,            
@CSST_DETA_RUBR TD_VC_001,            
/* Sub-Rubros */            
@CSCO_SUBR  TD_VC_003,            
@CSDE_SUBR  TD_VC_100,            
@CSST_REPO_SUBR TD_VC_001,            
@CNNU_ORDE_SUBR TD_IN_001,            
            
@VNIM_SALD_INIC TD_NU_016_004 ,  
@VNIM_SALD_ANTE TD_NU_016_004 ,  
@VSIM_SALD_ANTE TD_VC_010  
            
----   SETEO @VNNU_MESE CON EL MES INICIAL ---------            
Select  @VNNU_MESE = @INNU_MESE_INIC, @VNNU_ANIO = @INNU_ANIO_INIC,             
  @VNNU_CONT_MESE = 1            
            
Select  @VSNU_MESE_INIC = Replicate ('0', 2- Len(RTrim(LTrim(Str(@VNNU_MESE))))) + RTrim(LTrim(Str(@VNNU_MESE))),            
  @VSNU_ANIO_INIC = RTrim(LTrim(Str(@VNNU_ANIO)))            
            
            
            
            
/* Se forma el Nombre del Campo (IM_ME01) que correspondera al IMPORTE DEL MES 1,            
 En el LOOP se Forma en la Cadena @VSDE_CAMP el Nombre de las Columnas mes x mes */            
Select @VSDE_CAMP = ' IM_ME' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_MESE))))) +RTRIM(LTRIM(STR(@VNNU_CONT_MESE)))            
            
--- LOOP PARA LLENAR LA TABLA TEMPORAL MES POR MES, DESDE EL MES 1 AL MES 12 -------------------            
WHILE @VNNU_CONT_MESE <= 12            
BEGIN            
 Select  @VSNU_MESE = CONVERT(CHAR(2), @VNNU_MESE),            
  @VSNU_ANIO = CONVERT(CHAR(4), @VNNU_ANIO)            
             
 ---- INSERTO LOS DATA MES POR MES, DESDE EL MES INICIAL ----------            
 EXECUTE('            
 INSERT INTO #TWFLUJ_CAJA_Q03_0            
  (CO_EMPR, CO_ARUB, CO_RUBR, CO_SUBR,             
   FE_OPER, FA_TIPO_CAMB, CO_MONE, IM_MOVI)            
 Select T1.CO_EMPR, TTARUB_FINA.CO_ARUB, TTRUBR_FINA.CO_RUBR, TTSUBR_FINA.CO_SUBR,             
  T1.FE_OPER, T1.FA_TIPO_CAMB, T5.CO_MONE,             
  Round(             
   ( Dbo.FN_TCFACT_CAMB_Q02(T5.CO_MONE, "' + @ISCO_MONE + '", T1.FE_OPER,            
   T1.IM_MOVI * ( PATINDEX(T1.TI_MOVI_BANC, "I") - PATINDEX(T1.TI_MOVI_BANC, "E")),             
   T6.TI_CAMB, T6.TI_ORIG, "S", T6.CO_MONE_DEFA, T6.CO_MONE_EXTR, T1.FA_TIPO_CAMB)), 2)            
 From TCMOVI_BANC T1, TTSUBR_FINA, TTRUBR_FINA, TTARUB_FINA, TMCNTA_BANC T5, TMPARA_TESO T6            
 Where            
  MONTH(T1.FE_OPER) = CONVERT(INT, ' + @VSNU_MESE + ')            
 And YEAR(T1.FE_OPER) = CONVERT(INT, ' + @VSNU_ANIO + ')            
 And T1.TI_SITU = "ACT"            
            
 And T1.CO_EMPR = T5.CO_EMPR       
 And T1.CO_BANC = T5.CO_BANC            

 And T1.NU_CNTA_BANC = T5.NU_CNTA_BANC            
 And T1.CO_EMPR = T6.CO_EMPR            
            
 And T1.CO_EMPR = TTSUBR_FINA.CO_EMPR            
 And T1.CO_SUBR = TTSUBR_FINA.CO_SUBR            
            
 And TTSUBR_FINA.CO_EMPR = TTRUBR_FINA.CO_EMPR            
 And TTSUBR_FINA.CO_ARUB = TTRUBR_FINA.CO_ARUB            
 And TTSUBR_FINA.CO_RUBR = TTRUBR_FINA.CO_RUBR            
            
 And TTRUBR_FINA.CO_EMPR = TTARUB_FINA.CO_EMPR            
 And TTRUBR_FINA.CO_ARUB = TTARUB_FINA.CO_ARUB            
            
 And TTSUBR_FINA.ST_REPO = "S"            
            
 UNION ALL            
 Select T1.CO_EMPR, TTARUB_FINA.CO_ARUB, TTRUBR_FINA.CO_RUBR, TTSUBR_FINA.CO_SUBR,            
   T1.FE_OPER, T1.FA_TIPO_CAMB, T5.CO_MONE,            
   Round( ( Dbo.FN_TCFACT_CAMB_Q02(T5.CO_MONE, "' + @ISCO_MONE + '", T1.FE_OPER,            
   T1.IM_MOVI * ( PATINDEX(T1.TI_MOVI, "I") - PATINDEX(T1.TI_MOVI, "E")),             
   T6.TI_CAMB, T6.TI_ORIG, "S", T6.CO_MONE_DEFA, T6.CO_MONE_EXTR, T1.FA_TIPO_CAMB)), 2)            
 From TCMOVI_CAJA T1, TTSUBR_FINA, TTRUBR_FINA, TTARUB_FINA, TMCAJA T5, TMPARA_TESO T6            
 Where            
  MONTH(T1.FE_OPER) = CONVERT(INT, ' + @VSNU_MESE + ')             
 And YEAR(T1.FE_OPER) = CONVERT(INT, ' + @VSNU_ANIO + ')             
 And T1.TI_SITU = "ACT"  
            
 And T1.CO_EMPR = T5.CO_EMPR            
 And T1.CO_CAJA = T5.CO_CAJA            
 And TTSUBR_FINA.CO_EMPR = T6.CO_EMPR            
            
 And T1.CO_EMPR = TTSUBR_FINA.CO_EMPR            
 And T1.CO_SUBR = TTSUBR_FINA.CO_SUBR            
            
 And TTSUBR_FINA.CO_EMPR = TTRUBR_FINA.CO_EMPR            
 And TTSUBR_FINA.CO_ARUB = TTRUBR_FINA.CO_ARUB            
 And TTSUBR_FINA.CO_RUBR = TTRUBR_FINA.CO_RUBR            
            
 And TTRUBR_FINA.CO_EMPR = TTARUB_FINA.CO_EMPR            
 And TTRUBR_FINA.CO_ARUB = TTARUB_FINA.CO_ARUB            
            
 And TTSUBR_FINA.ST_REPO = "S" ')            
            
            
 ----- INSERTO EN OTRA TABLA TEMPORAL EL IMPORTE CONVERTIDO EN EL PERIODO CORRESPONDIENTE ----------------            
 EXECUTE('            
 INSERT INTO #TWFLUJ_CAJA_Q03_1            
 (CO_EMPR, CO_ARUB, CO_RUBR, CO_SUBR, ' + @VSDE_CAMP + ', NU_MESE)            
 Select CO_EMPR, CO_ARUB, CO_RUBR, CO_SUBR, IM_MOVI, CONVERT(CHAR(2), "' + @VNNU_CONT_MESE  + '")             
 From #TWFLUJ_CAJA_Q03_0  
 Union All   
 Select CO_EMPR, CO_ARUB, CO_RUBR, CO_SUBR, 0.00, CONVERT(CHAR(2), "' + @VNNU_CONT_MESE  + '")             
 From   TTSUBR_FINA  
 Where  
 Not Exists ( Select CO_EMPR  
   From #TWFLUJ_CAJA_Q03_0  
   Where  
   #TWFLUJ_CAJA_Q03_0.CO_EMPR = TTSUBR_FINA.CO_EMPR And   
   #TWFLUJ_CAJA_Q03_0.CO_ARUB = TTSUBR_FINA.CO_ARUB And   
   #TWFLUJ_CAJA_Q03_0.CO_RUBR = TTSUBR_FINA.CO_RUBR And   
   #TWFLUJ_CAJA_Q03_0.CO_SUBR = TTSUBR_FINA.CO_SUBR)   
')            
             
 ----- BORRA LA TABLA TEMPORAL ORIGEN PARA LLENARLA CON LA DATA DEL SIGUIENTE PERIODO -----------------            
 Delete #TWFLUJ_CAJA_Q03_0            
            
 Select @VNNU_MESE = @VNNU_MESE + 1, @VNNU_CONT_MESE = @VNNU_CONT_MESE + 1            
 IF @VNNU_MESE = 13            
 BEGIN            
  Select @VNNU_MESE = 1            
  Select @VNNU_ANIO = @VNNU_ANIO + 1            
 END            
 Select @VSDE_CAMP = ' IM_ME' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_MESE))))) +RTRIM(LTRIM(STR(@VNNU_CONT_MESE)))            
             
END            
            
Delete #TWFLUJ_CAJA_Q03_3            
            
---- Se Trabaja en Base al Cursor del AGRUPAMIENTOS            
Declare CU_SP_TTSUBR_FINA_Q03_1 Cursor                  
For                  
Select   
CO_ARUB,    DE_ARUB,    ST_REPO,    ST_FLUJ_ECON,   
ST_FLUJ_FINA,   ST_SALD_INIC,   ST_DETA,    NU_ORDE            
From TTARUB_FINA            
Order By NU_ORDE            
Open CU_SP_TTSUBR_FINA_Q03_1            
Fetch CU_SP_TTSUBR_FINA_Q03_1 Into   
@CSCO_ARUB,   @CSDE_ARUB,   @CSST_REPO_ARUB,  @CSST_FLUJ_ECON,   
@CSST_FLUJ_FINA,  @CSST_SALD_INIC,  @CSST_DETA_ARUB,  @CNNU_ORDE_ARUB            
            
While (@@FETCH_STATUS = 0)                  
Begin                  
 If (@@FETCH_STATUS  < 0)                  
 Begin                  
  Close CU_SP_TTSUBR_FINA_Q03_1                  
  Deallocate CU_SP_TTSUBR_FINA_Q03_1                  
  Return            
 End                  
            
 --- Saldos iniciales             
/* If @CSST_SALD_INIC = 'S'            
 Begin            
  Insert Into #TWFLUJ_CAJA_Q03_3  
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA, IM_ME01 )  
  Values (@CSDE_ARUB, 'A', @CNNU_ORDE_ARUB, 'D', @INIM_SALD_INIC)  
 End --- Fin de los Saldos Iniciales           */  
 -- Verifica que NO muestre Totales            
-- Else if @CSST_REPO_ARUB = 'N'            
 If @CSST_REPO_ARUB = 'S'   
 Begin     
   Insert Into #TWFLUJ_CAJA_Q03_3  
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA )  
  Values (@CSDE_ARUB, 'D', @CNNU_ORDE_ARUB, 'CA')            
 End            
 --- Saldos iniciales             
 If @CSST_SALD_INIC = 'S'            
 Begin            
  Insert Into #TWFLUJ_CAJA_Q03_3  
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA, IM_ME01 )  
  Values (@CSDE_ARUB, 'A', @CNNU_ORDE_ARUB, 'D', @INIM_SALD_INIC)  
 End --- Fin de los Saldos Iniciales             
            
 --- FIN Saldos iniciales             
             
 If @CSST_DETA_ARUB = 'S' --- Detalle x AGRUPAMIENTO            
 Begin             
             
  Declare CU_SP_TTSUBR_FINA_Q03_2 Cursor                  
  For                  
  Select CO_RUBR, DE_RUBR, ST_REPO, ST_DETA, NU_ORDE            
  From TTRUBR_FINA            
  Order By NU_ORDE            
  Open CU_SP_TTSUBR_FINA_Q03_2            
  Fetch CU_SP_TTSUBR_FINA_Q03_2 Into @CSCO_RUBR, @CSDE_RUBR, @CSST_REPO_RUBR, @CSST_DETA_RUBR, @CNNU_ORDE_RUBR            
              
  While (@@FETCH_STATUS = 0)                  
  Begin                  
   If (@@FETCH_STATUS  < 0)                  
   Begin                  
    --RAISERROR 20001 "FIN DE ARCHIVO"                  
    Close CU_SP_TTSUBR_FINA_Q03_2            
    Deallocate CU_SP_TTSUBR_FINA_Q03_2            
             
    Close CU_SP_TTSUBR_FINA_Q03_1                  
    Deallocate CU_SP_TTSUBR_FINA_Q03_1                  
    Return            
   End                  
             
   If @CSST_DETA_RUBR = 'S' --- Detalle x RUBRO            
   Begin             
             
    Declare CU_SP_TTSUBR_FINA_Q03_3 Cursor                  
    For                  
    Select CO_SUBR, DE_SUBR, NU_ORDE, ST_REPO            
    From TTSUBR_FINA            
    Order By NU_ORDE            
    Open CU_SP_TTSUBR_FINA_Q03_3            
    Fetch CU_SP_TTSUBR_FINA_Q03_3 Into @CSCO_SUBR, @CSDE_SUBR, @CNNU_ORDE_SUBR, @CSST_REPO_SUBR            
                
    While (@@FETCH_STATUS = 0)                  
    Begin                  
     If (@@FETCH_STATUS  < 0)                  
     Begin                  
      Close CU_SP_TTSUBR_FINA_Q03_3            
      Deallocate CU_SP_TTSUBR_FINA_Q03_3            
              
      Close CU_SP_TTSUBR_FINA_Q03_2            
      Deallocate CU_SP_TTSUBR_FINA_Q03_2            
               
      Close CU_SP_TTSUBR_FINA_Q03_1                  
      Deallocate CU_SP_TTSUBR_FINA_Q03_1                  
      Return            
     End                  
               
     Insert Into #TWFLUJ_CAJA_Q03_3            
     ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,             
       IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
       IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
       IM_ME09, IM_ME10, IM_ME11, IM_ME12 )            
     Select Max(@CSDE_SUBR),  Max('E'),        Max(@CNNU_ORDE_SUBR),  Max('D'),          
      Sum(IsNull(IM_ME01,0)), Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
      Sum(IsNull(IM_ME05,0)), Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
      Sum(IsNull(IM_ME09,0)), Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
     From #TWFLUJ_CAJA_Q03_1            
     Where            
     CO_EMPR = @ISCO_EMPR And            
     CO_ARUB = @CSCO_ARUB And            
     CO_RUBR = @CSCO_RUBR And            
     CO_SUBR = @CSCO_SUBR            
     Group By CO_EMPR, CO_ARUB, CO_RUBR, CO_SUBR            
              
                
     Fetch CU_SP_TTSUBR_FINA_Q03_3 Into @CSCO_SUBR, @CSDE_SUBR, @CNNU_ORDE_SUBR, @CSST_REPO_SUBR      
               
    End -- Fin Cursor del SUB-RUBROS            
    Close CU_SP_TTSUBR_FINA_Q03_3                  
    Deallocate CU_SP_TTSUBR_FINA_Q03_3                  
            
   End -- Control DETALLE x RUBRO            
              
   -- Muestra Totales O No AGRUPAMIENTO            
   If @CSST_REPO_RUBR = 'S' And @CSST_DETA_RUBR  = 'S'             
   Begin             
    Insert Into #TWFLUJ_CAJA_Q03_3            
    ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,          
      IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
      IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
      IM_ME09, IM_ME10, IM_ME11, IM_ME12 )            
    Select Max('     TOTAL-' + @CSDE_RUBR), Max('D'), Max(@CNNU_ORDE_RUBR),  Max('R1'),          
     Sum(IsNull(IM_ME01,0)), Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
     Sum(IsNull(IM_ME05,0)), Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
     Sum(IsNull(IM_ME09,0)), Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
    From #TWFLUJ_CAJA_Q03_1            
    Where            
    CO_EMPR = @ISCO_EMPR And            
    CO_ARUB = @CSCO_ARUB And            
    CO_RUBR = @CSCO_RUBR            
    Group By CO_EMPR, CO_ARUB, CO_RUBR            
          
          
   End             
   If @CSST_REPO_RUBR = 'S' And @CSST_DETA_RUBR  = 'N'             
   Begin             
    Insert Into #TWFLUJ_CAJA_Q03_3            
    ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,          
     IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
     IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
     IM_ME09, IM_ME10, IM_ME11, IM_ME12 )            
    Select Max('TOTAL ' + @CSDE_RUBR), Max('C'), Max(@CNNU_ORDE_RUBR),       Max('R2'),          
     Sum(IsNull(IM_ME01,0)), Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
     Sum(IsNull(IM_ME05,0)), Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
     Sum(IsNull(IM_ME09,0)), Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
    From #TWFLUJ_CAJA_Q03_1            
    Where            
    CO_EMPR = @ISCO_EMPR And            
    CO_ARUB = @CSCO_ARUB And            
    CO_RUBR = @CSCO_RUBR            
    Group By CO_EMPR, CO_ARUB, CO_RUBR            
              
   End                    
   --- Fin de Muestra Totales O No            
   Fetch CU_SP_TTSUBR_FINA_Q03_2 Into @CSCO_RUBR, @CSDE_RUBR, @CSST_REPO_RUBR, @CSST_DETA_RUBR, @CNNU_ORDE_RUBR       
             
  End -- Fin Cursor del RUBROS            
  Close CU_SP_TTSUBR_FINA_Q03_2                  
  Deallocate CU_SP_TTSUBR_FINA_Q03_2                  
            
 End -- Control DETALLE            
            
            
 If @CSST_REPO_ARUB = 'S' -- Muestra Totales O No AGRUPAMIENTO            
 Begin             
  Insert Into #TWFLUJ_CAJA_Q03_3            
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,          
   IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
   IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
   IM_ME09, IM_ME10, IM_ME11, IM_ME12            
   )            
  Select Max('T O T A L ' + @CSDE_ARUB), Max('E'), Max(@CNNU_ORDE_ARUB),  Max('R3'),          
   Sum(IsNull(IM_ME01,0)), Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
   Sum(IsNull(IM_ME05,0)), Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
   Sum(IsNull(IM_ME09,0)), Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
  From #TWFLUJ_CAJA_Q03_1            
  Where            
  CO_EMPR = @ISCO_EMPR And            
  CO_ARUB = @CSCO_ARUB            
  Group By CO_EMPR, CO_ARUB            
    
-----    
if (Not Exists (Select * From #TWFLUJ_CAJA_Q03_3 Where TI_DETA = 'R4' And ST_FLAG = 'F')And Exists (Select * From #TWFLUJ_CAJA_Q03_3 Where TI_DETA = 'R3' And NU_ORDE = 3) )    
Begin    
  Insert Into #TWFLUJ_CAJA_Q03_3    
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,          
   IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
   IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
   IM_ME09, IM_ME10, IM_ME11, IM_ME12            
   )            
  Select Max('FLUJO DE CAJA ECONOMICO ' ), Max('F'), Max(@CNNU_ORDE_ARUB),  Max('R4'),    
   Sum(IsNull(IM_ME01,0)),   Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
   Sum(IsNull(IM_ME05,0)),   Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
   Sum(IsNull(IM_ME09,0)),   Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
  From #TWFLUJ_CAJA_Q03_3    
  Where TI_DETA = 'R3'    
  And NU_ORDE in (2,3)    
  Group By TI_DETA    
End    
    
           
 End --- Fin de Muestra Totales O No            
If Exists (Select * From #TWFLUJ_CAJA_Q03_3 Where TI_DETA = 'D' And ST_FLAG = 'E' And NU_ORDE = 68)             
  Insert Into #TWFLUJ_CAJA_Q03_3    
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,          
   IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
   IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
   IM_ME09, IM_ME10, IM_ME11, IM_ME12            
   )            
  Select Max('RESULTADO FINANCIERO ' ), Max('G'), Max(@CNNU_ORDE_ARUB),  Max('R4'),    
   Sum(IsNull(IM_ME01,0)),   Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
   Sum(IsNull(IM_ME05,0)),   Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
   Sum(IsNull(IM_ME09,0)),   Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
  From #TWFLUJ_CAJA_Q03_3    
  Where TI_DETA = 'D'    
  And ST_FLAG = 'E'
  And NU_ORDE in (61, 66, 67, 68)    
  Group By TI_DETA    


 Fetch CU_SP_TTSUBR_FINA_Q03_1 Into @CSCO_ARUB, @CSDE_ARUB, @CSST_REPO_ARUB, @CSST_FLUJ_ECON, @CSST_FLUJ_FINA, @CSST_SALD_INIC, @CSST_DETA_ARUB, @CNNU_ORDE_ARUB            
            
End -- Fin Cursor del AGRUPAMIENTOS            
Close CU_SP_TTSUBR_FINA_Q03_1                  
Deallocate CU_SP_TTSUBR_FINA_Q03_1                  
          
  Insert Into #TWFLUJ_CAJA_Q03_3    
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,          
   IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
   IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
   IM_ME09, IM_ME10, IM_ME11, IM_ME12            
   )            
  Select Max('FLUJO DE CAJA FINANCIERO ' ), Max('A'), Max(@CNNU_ORDE_ARUB),  Max('R5'),    
   Sum(IsNull(IM_ME01,0)),   Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
   Sum(IsNull(IM_ME05,0)),   Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
   Sum(IsNull(IM_ME09,0)),   Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
  From #TWFLUJ_CAJA_Q03_3    
  Where TI_DETA = 'R4'  
  Group By TI_DETA    
  
  
  Insert Into #TWFLUJ_CAJA_Q03_3    
  ( DE_ARUB, ST_FLAG, NU_ORDE, TI_DETA,          
   IM_ME01, IM_ME02, IM_ME03, IM_ME04,            
   IM_ME05, IM_ME06, IM_ME07, IM_ME08,            
   IM_ME09, IM_ME10, IM_ME11, IM_ME12            
   )            
  
  Select Max('STOCK ANUAL - '+ CAST( @INNU_ANIO_INIC AS VARCHAR(04))) , Max('G'), Max(@CNNU_ORDE_ARUB),  Max('R6'),    
   Sum(IsNull(IM_ME01,0)),   Sum(IsNull(IM_ME02,0)), Sum(IsNull(IM_ME03,0)), Sum(IsNull(IM_ME04,0)),            
   Sum(IsNull(IM_ME05,0)),   Sum(IsNull(IM_ME06,0)), Sum(IsNull(IM_ME07,0)), Sum(IsNull(IM_ME08,0)),            
   Sum(IsNull(IM_ME09,0)),   Sum(IsNull(IM_ME10,0)), Sum(IsNull(IM_ME11,0)), Sum(IsNull(IM_ME12,0))            
  From #TWFLUJ_CAJA_Q03_3  
  Where ST_FLAG = 'A'  
  Group By ST_FLAG    
  
  
----   SETEO @VNNU_MESE CON EL MES INICIAL ---------            
Select  @VNNU_MESE = @INNU_MESE_INIC, @VNNU_ANIO = @INNU_ANIO_INIC,             
  @VNNU_CONT_MESE = 2  
            
Select  @VSNU_MESE_INIC = Replicate ('0', 2- Len(RTrim(LTrim(Str(@VNNU_MESE))))) + RTrim(LTrim(Str(@VNNU_MESE))),            
  @VSNU_ANIO_INIC = RTrim(LTrim(Str(@VNNU_ANIO)))            
           
            
            
            
/* Se forma el Nombre del Campo (IM_ME01) que correspondera al IMPORTE DEL MES 1,            
 En el LOOP se Forma en la Cadena @VSDE_CAMP el Nombre de las Columnas mes x mes */            
  
Select @VSDE_CAMP_ANTE = ' IM_ME' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_MESE - 1))))) + RTRIM(LTRIM(STR(@VNNU_CONT_MESE - 1)))  
Select @VSDE_CAMP = ' IM_ME' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_MESE))))) + RTRIM(LTRIM(STR(@VNNU_CONT_MESE)))   
  
       
            
--- LOOP PARA LLENAR LA TABLA TEMPORAL MES POR MES, DESDE EL MES 1 AL MES 12 -------------------            
WHILE @VNNU_CONT_MESE < 13  
BEGIN            
  
 -- Carga los Saldos Iniciales ----   
 Execute( '  
  Update #TWFLUJ_CAJA_Q03_3   
  Set ' + @VSDE_CAMP + ' = ( Select '+ @VSDE_CAMP_ANTE+'  
						      From #TWFLUJ_CAJA_Q03_3  
						      Where ST_FLAG = "G"   
						      And TI_DETA = "R6")  
  Where ST_FLAG = "A"   
  And TI_DETA = "D"     
  ')  
   
 -- Recalcula el Stock Anual ---- 
 Execute( '  
  Update #TWFLUJ_CAJA_Q03_3   
  Set ' + @VSDE_CAMP + ' = ' + @VSDE_CAMP + ' + 
							( Select '+ @VSDE_CAMP_ANTE+'  
						      From #TWFLUJ_CAJA_Q03_3  
						      Where ST_FLAG = "G"   
						      And TI_DETA = "R6")  
  Where ST_FLAG = "G"   
  And TI_DETA   = "R6"     
  ')  

  
 select @VNNU_CONT_MESE = @VNNU_CONT_MESE  +1  
  
 Select @VSDE_CAMP_ANTE = ' IM_ME' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_MESE - 1))))) + RTRIM(LTRIM(STR(@VNNU_CONT_MESE - 1)))  
 Select @VSDE_CAMP = ' IM_ME' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_MESE))))) + RTRIM(LTRIM(STR(@VNNU_CONT_MESE)))   
  
  
END            


Select	DE_ARUB, IM_ME01, IM_ME02, IM_ME03, IM_ME04, IM_ME05, IM_ME06, IM_ME07, IM_ME08, IM_ME09, IM_ME10, IM_ME11,   
		IM_ME12,
		IM_ME01 + IM_ME02 + IM_ME03 + IM_ME04 + IM_ME05 + IM_ME06 +
		IM_ME07 + IM_ME08 + IM_ME09 + IM_ME10 + IM_ME11 + IM_ME12 IM_TOTA, 
		NU_ORDE, ST_FLAG, TI_DETA
From	#TWFLUJ_CAJA_Q03_3            
  
            
RETURN            
/********************************* FIN ***********************************/


GO
